package com.cennavi.bigdata.baotou.scripts;

import com.cennavi.bigdata.baotou.util.DateUtils;
import org.apache.commons.dbcp.BasicDataSource;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.text.SimpleDateFormat;
import java.util.*;

import static java.lang.System.out;

/**
 * Created by cennavi on 2017/10/23.
 */
public class DataConversion {

    private static final Logger LOGGER = LoggerFactory.getLogger(DataConversion.class);

    private static BasicDataSource basicDataSource = new BasicDataSource();

    /**
     * 获取数据库连接
     * @return
     */
    public static Connection getConnection(){
        Connection connection =null;
        try {
            basicDataSource.setDriverClassName("oracle.jdbc.driver.OracleDriver");
            basicDataSource.setUrl("jdbc:oracle:thin:@(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=15.32.12.203)(PORT=1521))(ADDRESS = (PROTOCOL = TCP)(HOST =15.32.12.203 )(PORT = 1521))(LOAD_BALANCE=yes)(CONNECT_DATA=(SERVER = DEDICATED)(SERVICE_NAME=jczhdb)))");
            basicDataSource.setUsername("btjczh");
            basicDataSource.setPassword("btjczh1314");
            connection = basicDataSource.getConnection();
           LOGGER.info("数据库连接成功");

        } catch (Exception e) {
            e.printStackTrace();
        }
        return connection;
    }

    /**
     * 定时建表方法
     */
    public void timingBuildTable(){
        Connection connection =null;
        PreparedStatement statement = null;
        ResultSet resultSet =null;
        PreparedStatement statement1 = null;
        PreparedStatement statement2 = null;
        PreparedStatement statement3 = null;
        PreparedStatement statement4 = null;
        connection = getConnection();
        String format = new SimpleDateFormat("yyyyMM").format(new Date());
        String sqlXJLL1 = "create table CTL_XJLL_MINUTE_HISTORY_"+format+" ( " +
                "  XJBH   VARCHAR2(32) not null, " +
                "  XJMC   VARCHAR2(60), " +
                "  XJLL   VARCHAR(38),"+
                "  TJSD   VARCHAR(38) " +
                ") " +
                "  tablespace BTJCZH  " ;
        String indexsql = "create index INDEX_CTL_" + format + "_MINUTE_TJSD on CTL_XJLL_MINUTE_HISTORY_"+format+" (TJSD)" +
                "  tablespace BTJCZH" +
                "  pctfree 10" +
                "  initrans 2" +
                "  maxtrans 255" +
                "  storage" +
                "  (" +
                "    initial 64K" +
                "    next 64K" +
                "    minextents 1" +
                "    maxextents unlimited" +
                "  )";
        String indexsql2 =   "create index INDEX_CTL_" + format + "_MINUTE_XJBH on CTL_XJLL_MINUTE_HISTORY_"+format+" (XJBH)" +
                "  tablespace BTJCZH" +
                "  pctfree 10" +
                "  initrans 2" +
                "  maxtrans 255" +
                "  storage" +
                "  (" +
                "    initial 64K" +
                "    next 64K" +
                "    minextents 1" +
                "    maxextents unlimited" +
                "  )" ;
        String sqlXJLL2 = "create table CTL_XJLL_HOUR_HISTORY_"+format+" ( " +
                "  XJBH   VARCHAR2(32) not null, " +
                "  XJMC   VARCHAR2(60), " +
                "  XJLL   VARCHAR(38),"+
                "  TJSD   VARCHAR(38) " +
                ") " +
                "  tablespace BTJCZH ";
        String indexsql3 = "create index INDEX_CTL_"+format+"_TJSD on CTL_XJLL_HOUR_HISTORY_"+format+" (TJSD)\n" +
                "  tablespace BTJCZH" +
                "  pctfree 10" +
                "  initrans 2" +
                "  maxtrans 255" +
                "  storage" +
                "  (" +
                "    initial 64K" +
                "    next 64K" +
                "    minextents 1" +
                "    maxextents unlimited" +
                "  )" ;
        String indexsql4 = "create index INDEX_CTL_"+format+"_XJBH on CTL_XJLL_HOUR_HISTORY_"+format+" (XJBH)" +
                "  tablespace BTJCZH" +
                "  pctfree 10" +
                "  initrans 2" +
                "  maxtrans 255" +
                "  storage" +
                "  (" +
                "    initial 64K" +
                "    next 1M" +
                "    minextents 1" +
                "    maxextents unlimited" +
                "  )";
        try {
            statement = connection.prepareStatement(sqlXJLL1);
            statement.execute();
            statement1 = connection.prepareStatement(indexsql);
            statement1.execute();
            statement2 = connection.prepareStatement(indexsql2);
            statement2.execute();

            statement= connection.prepareStatement(sqlXJLL2);
            statement.execute();
            statement3 = connection.prepareStatement(indexsql3);
            statement3.execute();
            statement4 = connection.prepareStatement(indexsql4);
            statement4.execute();
        } catch (SQLException e) {
            e.printStackTrace();
        }finally {
            try {
                if (statement1 !=null){statement1.close();}
                if (statement2 !=null){statement2.close();}
                if (statement3 !=null){statement3.close();}
                if (statement4 !=null){statement4.close();}
            }catch (Exception e){
                e.getStackTrace();
            }
            closeOracle(resultSet, statement, connection);
        }

    }


    /**
     * 定时删除表
     */
    public void dropTable(){
        Connection connection =null;
        PreparedStatement statement = null;
        ResultSet resultSet =null;
        connection = getConnection();
        Calendar calendar = Calendar.getInstance();
        calendar.add(Calendar.MONTH,-2);
        String format = new SimpleDateFormat("yyyyMM").format(calendar.getTime());
        String sqlXJLL1 = "DROP TABLE CTL_XJLL_MINUTE_HISTORY_" + format ;
        String sqlXJLL2 = "DROP TABLE CTL_XJLL_HOUR_HISTORY_" + format ;
        try {
            statement = connection.prepareStatement(sqlXJLL1);
            statement.execute();
            statement= connection.prepareStatement(sqlXJLL2);
            statement.execute();
        } catch (SQLException e) {
            e.printStackTrace();
        }finally {
            closeOracle(resultSet, statement, connection);
        }

    }
    /**
     * 相机整合过滤，并存入相机流量表(分钟表）。五分钟执行一次
     */
    public void timingDataConversionMinute()  {

        String format2 = DateUtils.DateFormatUnit.DATE_AND_TIME.getDateStr(new Date());
        LOGGER.info("timingDataConversionMinute开始时间：" + format2);
        Connection connection =null;
        PreparedStatement statement = null;
        ResultSet resultSet =null;
        Map<Object, Object> clzsMap = new HashMap<>();
        Set<String> strings = new HashSet<>();
        //循环合并后的相机名称集合
        Calendar calendar = Calendar.getInstance();
        String format1 = DateUtils.DateFormatUnit.DATE_AND_MIN_SEC.getDateStr(calendar.getTime());
        calendar.add(Calendar.MINUTE,-5);
        String format = DateUtils.DateFormatUnit.DATE_AND_MIN_SEC.getDateStr(calendar.getTime());
        String sql2 = "select t.dmmc dmmc,t.dmid dmid,sum(t.gcs) as sums  from CTL_TRAFFIC_FLOW_MINUTE_DM t " +
                "where t.tjsj >= to_date( ?,'yyyy/MM/dd HH24:mi:ss') and t.tjsj < to_date(?,'yyyy/MM/dd HH24:mi:ss') group by t.dmid,t.dmmc";
            try {
                    connection = getConnection();
                    statement = connection.prepareStatement(sql2);
                    statement.setString(1,format);
                    statement.setString(2,format1);
                    resultSet = statement.executeQuery();
                while (resultSet.next()) {
                    int txclzs = resultSet.getInt("sums");
                    String sbbh = resultSet.getString("dmid");
                    String dmmc = resultSet.getString("dmmc");
                    strings.add(sbbh);
                    clzsMap.put(sbbh+"mc",dmmc);
                    clzsMap.put(sbbh+"ll",txclzs);
                }
            } catch (Exception e) {
                e.printStackTrace();
            }finally {
                closeOracle(resultSet,statement,connection);
            }

            try {
                String timeTem = new SimpleDateFormat("yyyyMM").format(new Date());
                String insertSql = "insert into CTL_XJLL_MINUTE_HISTORY_" + timeTem + "(XJBH,XJMC,XJLL,TJSD)" +
                        "VALUES (? ,? ,? ,?)";
                StringBuffer sql = new StringBuffer(insertSql);
                sql.append(insertSql);
                connection = getConnection();
                statement = connection.prepareStatement(insertSql);
                Iterator<String> iterator = strings.iterator();
                while (iterator.hasNext()){
                    String next = iterator.next();
                    int xjll = (int) clzsMap.get(next+"ll");
                    String dmmc = (String) clzsMap.get(next + "mc");
                    statement.setString(1, next);
                    statement.setString(2, dmmc);
                    statement.setString(3, String.valueOf(xjll));
                    statement.setString(4, format1);
                    statement.addBatch();
                }
                statement.executeBatch();
            }catch (Exception e){
                e.getStackTrace();
                out.println("插入成功");
            }finally {
                closeOracle(resultSet,statement,connection);
                String format3 = DateUtils.DateFormatUnit.DATE_AND_TIME.getDateStr(new Date());
                LOGGER.info("timingDataConversionMinute结束时间：" + format3);
            }
        }



    /**
     * 相机整合过滤，并存入相机流量表(小时表）。一小时执行一次
     */
    public void timingDataConversionHour()  {
        String format2 = DateUtils.DateFormatUnit.DATE_AND_TIME.getDateStr(new Date());
        LOGGER.info("timingDataConversionHour开始时间：" + format2);
        Connection connection =null;
        PreparedStatement statement = null;
        ResultSet resultSet =null;

        Connection connection2 =null;
        PreparedStatement statement2 = null;
        ResultSet resultSet2 =null;

        Map<Object, Object> clzsMap = new HashMap<>();
        Set<String> strings = new HashSet<>();

        //循环合并后的相机名称集合
            Calendar instance = Calendar.getInstance();
            instance.add(Calendar.HOUR,-1);
            List<String> xjmcs = new ArrayList<>();
            String timeTem = new SimpleDateFormat("yyyyMM").format(new Date());
            String format = new SimpleDateFormat("yyyy/MM/dd HH:00:00").format(instance.getTime());
            String  selectSql = "select c.xjmc from CTL_XJLL_HOUR_HISTORY_"+timeTem+" c where c.tjsd=?";
            connection2= getConnection();
            try {
                statement2 = connection2.prepareStatement(selectSql);
                statement2.setString(1,format);
                resultSet2 = statement2.executeQuery();
                while (resultSet2.next()){
                    String xjmc = resultSet2.getString("xjmc");
                    xjmcs.add(xjmc);
                }
            } catch (SQLException e) {
                e.printStackTrace();
            }finally {
                closeOracle(resultSet2,statement2,connection2);
            }
            if (xjmcs.size()==0){
                connection = getConnection();
                String sql = "select c.GCS, c.DMID,c.DMMC from CTL_TRAFFIC_FLOW_HOUR_DM c where " +
                        " c.TJSJ = ? ";
                try {
                    Date date = new SimpleDateFormat("yyyy/MM/dd HH:00:00").parse(format);
                    java.sql.Timestamp date2 = new java.sql.Timestamp(date.getTime());
                    statement = connection.prepareStatement(sql);
                    statement.setTimestamp(1, date2);
                    resultSet = statement.executeQuery();
                    while (resultSet.next()) {
                        int txclzs = resultSet.getInt("GCS");//每个相机编号下的流量
                        String xjbh = resultSet.getString("DMID");
                        String dmmc = resultSet.getString("DMMC");
                        clzsMap.put(xjbh+"ll", txclzs);
                        clzsMap.put(xjbh+"mc",dmmc);
                        strings.add(xjbh);
                    }
                }catch (Exception e) {
                    e.getStackTrace();
                }finally {
                    closeOracle(resultSet,statement,connection);
                }
                try {
                    String insertSql = "insert into " + "CTL_XJLL_HOUR_HISTORY_" + timeTem + " (XJBH ,XJMC ,XJLL ,TJSD)" +
                            "VALUES (? ,? ,? ,? )";
                    StringBuffer sql2 = new StringBuffer(insertSql);
                    sql2.append(insertSql);
                    connection = getConnection();
                    statement = connection.prepareStatement(insertSql);
                    Iterator<String> iterator = strings.iterator();
                    while (iterator.hasNext()){
                        String next = iterator.next();
                        int xjll = (int) clzsMap.get(next + "ll");
                        String dmmc = (String) clzsMap.get(next + "mc");
                        statement.setString(1, next);
                        statement.setString(2, dmmc);
                        statement.setString(3, String.valueOf(xjll));
                        statement.setString(4, format);
                        statement.addBatch();
                    }
                    statement.executeBatch();
                } catch (Exception e) {
                    e.printStackTrace();
                }finally {
                    closeOracle(resultSet,statement,connection);
                    String format3 = DateUtils.DateFormatUnit.DATE_AND_TIME.getDateStr(new Date());
                    LOGGER.info("timingDataConversionHour结束时间：" + format3);
                }
            }else {
                LOGGER.info("该数据已存在！！！");
            }
        }
    /**
     * 关闭连接
     * @param result
     * @param pre
     * @param con
     */
    private static void closeOracle(ResultSet result, PreparedStatement pre, Connection con) {
        try {
            // 逐一将上面的几个对象关闭，因为不关闭的话会影响性能、并且占用资源
            // 注意关闭的顺序，最后使用的最先关闭
            if (result != null)
                result.close();
            if (pre != null)
                pre.close();
            if (con != null)
                con.close();
            LOGGER.info("数据库连接已关闭！");
        } catch (Exception e) {
            e.printStackTrace();
        }
    }

    }

